package com.atjava.nebula.core.util.excel;

import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;

import com.atjava.nebula.core.constant.Constant;
import com.atjava.nebula.core.exception.baseExp.SystemException;

/**
 * 
 * @author jin-dong
 *
 */
public class ExcelUtil {
	
	private static DateFormat dateFormat = new SimpleDateFormat(Constant.DEFAULT_DATE_FORMAT);

	public static String[][] readExcelSheetData(Workbook workBook, int sheetPage)
			throws SystemException {
		
		int rowNum = 0;
		int lineNum = 0;
		boolean effectiveFlg;
		
		List<Integer> blankRowList = new ArrayList<Integer>();
		Sheet hssfsheet = workBook.getSheetAt(sheetPage);
		rowNum = hssfsheet.getLastRowNum() + 1;
		lineNum = hssfsheet.getRow(0).getLastCellNum();
		String value[][] = new String[rowNum][lineNum];
		
		for (int j = 0; j < rowNum; j++) {
			effectiveFlg = false;
			Row hssfrow = hssfsheet.getRow(j);
			for (int i = 0; i < lineNum; i++) {
				Cell hssfcell = hssfrow.getCell(i);
				if (hssfcell != null && hssfcell.toString() != "") {
					switch (hssfcell.getCellType()) {
					case Cell.CELL_TYPE_NUMERIC:
						if (HSSFDateUtil.isCellDateFormatted(hssfcell)) {
							value[j][i] = dateFormat.format(hssfcell.getDateCellValue());
						} else {
							value[j][i] = String.valueOf(hssfcell.getNumericCellValue());
						}
						break;
					/*case Cell.CELL_TYPE_FORMULA:
						try {
							value[j][i] = String.valueOf(hssfcell.getNumericCellValue());
						}catch (IllegalStateException e) {
							value[j][i] = String.valueOf(hssfcell.getRichStringCellValue());
						}
						break;*/
					default:
						value[j][i] = hssfcell.toString();

					}
					effectiveFlg = true;

				} else {
					value[j][i] = "";
				}
			}
			if (!effectiveFlg) {
				blankRowList.add(j);
			}
		}

		if (blankRowList.size() != 0) {
			return getEffectiveExcelData(value, blankRowList, lineNum);
		}
		return value;
	}

	private static String[][] getEffectiveExcelData(String value[][],
			List<Integer> rowList, int lineNum) {
		int effectiveRow = value.length - rowList.size();
		String effectiveValue[][] = new String[effectiveRow][lineNum];
		int index = 0;
		for (int i = 0; i < value.length; i++) {
			if (rowList.contains(i)) {
				continue;
			} else {
				effectiveValue[index] = value[i];
				index++;
			}
		}
		return effectiveValue;
	}

	public static boolean isExcelFile(String fileName) {
		String extension = StringUtils.getFilenameExtension(fileName);
		if (fileName == null || "".equals(fileName))
			return false;
		return extension.equalsIgnoreCase("xls")
				|| extension.equalsIgnoreCase("xlsx");
	}

	public static Workbook getExcelWorkbook(InputStream is, String fileName)
			throws SystemException {
		String extension = StringUtils.getFilenameExtension(fileName);
		try {
			// 2003
			if ("xls".equalsIgnoreCase(extension)) {
				return new HSSFWorkbook(is);
				// 2007
			} else if ("xlsx".equalsIgnoreCase(extension)) {
				return new XSSFWorkbook(is);
			}
			throw new IOException();
				
		} catch (Exception e) {
			throw new SystemException("The file [" + fileName + "] is not a Excel file!");
		}
	}

	public static Map<String, String[][]> readExcelData(InputStream is,
			String fileName, Integer[] indices) throws SystemException {
		Map<String, String[][]> excelData = new HashMap<String, String[][]>();
		Workbook workbook = getExcelWorkbook(is, fileName);
		for (int i = 0; i < indices.length; i++) {
			String[][] value = readExcelSheetData(workbook, indices[i]);
			String sheetName = workbook.getSheetName(indices[i]);
			excelData.put(sheetName, value);
		}
		return excelData;
	}

	public static Map<String, String[][]> readExcelData(InputStream is,
			String fileName) throws SystemException {
		Map<String, String[][]> excelData = new HashMap<String, String[][]>();
		Workbook workbook = getExcelWorkbook(is, fileName);
		int sheets = workbook.getNumberOfSheets();
		for (int i = 0; i < sheets; i++) {
			String[][] value = readExcelSheetData(workbook, i);
			String sheetName = workbook.getSheetName(i);
			excelData.put(sheetName, value);
		}
		return excelData;
	}

	public static String[][] readExcelData(InputStream is, String fileName,
			int sheetIndex) throws SystemException {
		Workbook workbook = getExcelWorkbook(is, fileName);
		return readExcelSheetData(workbook, sheetIndex);
	}

	private static String getRightString(String sNum) {
		DecimalFormat format = new DecimalFormat("#.00");
		String resultStr = format.format(new Double(sNum));
		if (resultStr.matches("^[-+]?\\d+\\.[0]+$")) {
			resultStr = resultStr.substring(0, resultStr.indexOf("."));
		}
		return resultStr;
	}

}
